package zdy.web.control;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import zdy.web.bean.User;
import zdy.web.orm.JDBCPool;

/**
 * Servlet implementation class UserServlet
 */
@WebServlet("/c/user")
public class UserServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public UserServlet() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub

		request.setCharacterEncoding("utf-8");
		// System.out.println("hhhhh ");
		User u = new User();
		u.setId(Fun.getInt(request.getParameter("id")));
		u.setName(request.getParameter("name1"));
		u.setAge(Fun.getInt(request.getParameter("age1")));

		List<User> out_us = null;
		String m = request.getParameter("med");
		int step = 0;

		if (m != null) {
			char c = m.toCharArray()[0];
			switch (c) {
			case 'I':
				insert(u);
				break;
			case 'U':
				update(u);
				break;
			case 'D':
				delete(u);
				break;
			case 'O':
				getOne(u);
				step = 1;
				break;
			}
		}
		if (step == 1) {
			request.setAttribute("one", u);
		} else {
			out_us = getAll();
			request.setAttribute("list", out_us);
		}

		RequestDispatcher rd = request.getRequestDispatcher("/jsp/day9/list.jsp");
		rd.forward(request, response);

	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

	// 添加
	public int insert(User user) {

		Connection conn = JDBCPool.getConnection();
		int i = 0;
		String sql = "insert into user (Name,Age) values(?,?)";
		//
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);

			pstmt.setString(1, user.getName());
			pstmt.setInt(2, user.getAge());
			i = pstmt.executeUpdate();

			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println(user.getName());
			e.printStackTrace();

		}
		return i;
	}

	// 删除
	public int delete(User u) {
		Connection conn = JDBCPool.getConnection();
		int i = 0;
		String sql = "delete from user where Name='" + u.getName() + "'";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();

			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	// 更新
	public int update(User t) {
		Connection conn = JDBCPool.getConnection();
		int i = 0;
		String sql = "update user set Age=" + t.getAge() + " where Name='" + t.getName() + "'";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			i = pstmt.executeUpdate();

			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	// 根据id查询
	public void getOne(User u) {
		Connection conn = JDBCPool.getConnection();
		String sql = "select id,name,age from user where id=?";
		PreparedStatement pstmt;
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setInt(1, u.getId());
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				u.setName(rs.getString(2));
				u.setAge(rs.getInt(3));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 查询所有
	public List<User> getAll() {
		Connection conn = JDBCPool.getConnection();
		String sql = "select name,id,age from user";
		PreparedStatement pstmt;
		List<User> users = new ArrayList<User>();
		try {
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			int col = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				User u = new User();
				for (int i = 1; i <= col; i++) {
					u.setName(rs.getString("name"));
					u.setAge(rs.getInt("age"));
					u.setId(rs.getInt("id"));
				}
				users.add(u);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}
}
